<?php

/*
Plugin Name: Synergy Project Management
Plugin URI: http://URI_Of_Page_Describing_Plugin_and_Updates
Description: Plugin for managing projectssyn
Version: 1.0
Author: Sowmya Sankar, Weranga Kaluarachchi
Author URI: http://URI_Of_The_Plugin_Author

*/

// import javascript file into syn_proj_mgmt plugin
wp_enqueue_script('syn_project_mgmt_script', '/wp-content/plugins/syn_proj_mgmt/syn_proj_mgmt.js');


/**
 * Main method of the syn_proj_mgmt plugin
 */
function widget_syn_proj_mgmt() {
	global $wpdb;
	global $current_user;
	echo $before_widget; 
 	echo $before_title . "<h4>Projects </h4>". $after_title;
	if(isset($_POST[hdProjID]) || ($_POST[hdProjID] != "")) {    
   	   $id = $_POST[hdProjID];
   	   if(isset($_POST[btnSynProjUpdate]) || ($_POST[btnSynProjUpdate] == "UpdateProject")) {
       		update_project();
       }
       else if(isset($_POST[btnSynProjDelete]) || ($_POST[btnSynProjDelete] == "DeleteProject")) {
      	 	delete_project($_POST[hdProjID]);
       }
	   else if(isset($_POST[btnSynProjUpdateContributor]) || ($_POST[btnSynProjUpdateContributor] == "UpdateProjectContributor")) {
      	 	modify_project_contributors($_POST[hdProjID]);     	 	
       }      
   	 	  
    }
	if(isset($_POST[btnSynProjAdd]) || ($_POST[btnSynProjAdd] == "AddProject")) {
		add_new_project();			
	}
	display_new_project();  
	if(isset($_POST[syn_project_select_id]) || ($_POST[syn_project_select_id] != "")) {
		syn_set_current_project($_POST[syn_project_select_id]);
	}	

  	
	$proj_table = $wpdb->prefix."projects";
	$user_table = $wpdb->prefix."users";
	$user_meta  = $wpdb->prefix."usermeta";
	$projectFlag = false;
	$i = 0;	
	foreach (get_projects_by_contributorid($current_user->ID) as $r) {
			$manager = $wpdb->get_row("SELECT a.meta_value as firstname, b.meta_value as lastname " .
		         "FROM $user_meta as a, $user_meta as b " .
				 "WHERE a.user_id = $r->MANAGER_ID AND b.user_id = $r->MANAGER_ID " .
				 "AND a.meta_key = 'first_name' AND b.meta_key = 'last_name' "); 
			echo "<div class='project'>";
			if($r->MANAGER_ID == $current_user->ID) {
				$projTimestamp = convert_project_datetime($r->PROJ_DATE);
				if(syn_get_current_project() == $r->PROJ_ID) {
					echo "<div class='current myproject'>";
				}
				else {
					echo "<div class='myproject'>";
				}				
				echo '<p><b><a onclick=selectProj(' . $r->PROJ_ID .'); href="#">' .$r->PROJ_NAME .'</a></b><span onclick="toggleProject(\'project' . 
						$r->PROJ_ID . '\', this, \'[-]\', \'[+]\');" style="cursor:pointer"> [+]</span>' . "";
				$projectFlag = true;	
				echo "<div id='manager' class='manager'>".$manager->firstname." ".$manager->lastname."</div>\n";			
				echo "<div id='project" . $r->PROJ_ID . "' style='display:none;'>\n"; 
				echo "<div id='class' id='date' >". date("F j, Y g:ia", $projTimestamp)."</div>\n";
				echo "<div class='contributor' id='contributor'>";		
				$i = 0;
				foreach (get_contributors_by_project($r->PROJ_ID) as $contrRow) {
					foreach (get_user_detail_byID($contrRow->CONTRIBUTOR_ID) as $contriDetail) {
						if ($i > 0) { echo ", "; } else { $i++; }
						echo $contriDetail->first_name." ".$contriDetail->last_name;
					}
				}
				echo "</div>";
				echo "<div class='projInfo' id='info'>". $r->PROJ_INFO . "</div>\n";
				$tmpKeyWordsArr = "";
				$tmpContributorsOfProject = "";
		
				$itemIndex = 0;
				$contrIndex = 0;
				echo "<div class='projCategory' >\n";
				foreach (get_keywords_by_projID($r->PROJ_ID) as $resKeyword) {			
					if($itemIndex == 0){
						echo $resKeyword->KEYWORD;		
						$tmpKeyWordsArr .= $resKeyword->KEY_ID;			
					}
					else {
				 		echo ", ".$resKeyword->KEYWORD;	
				 		$tmpKeyWordsArr .= "#".$resKeyword->KEY_ID;
					}
					
					$itemIndex++;		
				}			
				
				foreach (get_contributors_by_project($r->PROJ_ID) as $contributor) {			
					if($contrIndex == 0){
						$tmpContributorsOfProject .= $contributor->CONTRIBUTOR_ID;			
					}
					else {
				 		$tmpContributorsOfProject .= "#".$contributor->CONTRIBUTOR_ID;
					}
					
					$contrIndex++;		
				}
				echo "</div>\n";
				echo "<div class='btnPanel' id='btnPanel'><button id='btnSynProjModify$r->PROJ_ID' name='btnSynProjModify' class='link' onclick='editProjInfo($r->PROJ_ID);'>Modify</button><button id='btnSynProjAddContributor$r->PROJ_ID' onclick = 'updateContributorsOfProject($r->PROJ_ID);' class='link'>Add Contributor</button></div>";
				echo "<input type='hidden' name='tmpProjKeyword$r->PROJ_ID' id='tmpProjKeyword$r->PROJ_ID' value='$tmpKeyWordsArr'/>";			
				echo "<input type='hidden' name='tmpProjContributors$r->PROJ_ID' id='tmpProjContributors$r->PROJ_ID' value='$tmpContributorsOfProject'/>";			
				echo "<input type='hidden' name='projID$i' value='$r->PROJ_ID'/>";
				echo "<input type='hidden' name='tmpProjName$r->PROJ_ID' id='tmpProjName$r->PROJ_ID' value='$r->PROJ_NAME'/>";
				echo "<input type='hidden' name='tmpProjInfo$r->PROJ_ID' id='tmpProjInfo$r->PROJ_ID' value='$r->PROJ_INFO'/>";
				echo "<input type='hidden' name='projUpdateMode' id='projUpdateMode'/>";
				display_edit_projects($r->PROJ_ID);						
				$i++;
				echo "</div>";
	 		}
	 		else  {
	 			$projTimestamp = convert_project_datetime($r->PROJ_DATE);
	 			if(syn_get_current_project() == $r->PROJ_ID) {
					echo "<div class='current'>";
				}

	 			echo '<p><b><a onclick=selectProj(' . $r->PROJ_ID .'); href="#">' .$r->PROJ_NAME .'</a></b><span onclick="toggleProject(\'project' . 
						$r->PROJ_ID . '\', this, \'[-]\', \'[+]\');" style="cursor:pointer"> [+]</span>' . "";
				$projectFlag = true;	
				echo "<div id='manager'>".$manager->firstname." ".$manager->lastname."</div>\n\n";				
				
				echo "<div id='project" . $r->PROJ_ID . "' style='display:none;'>\n"; 
				echo "<div id='date'>". date("F j, Y g:ia",$projTimestamp)."</div>\n";
				echo "<div class='contributor' id='contributor'>";		
				$i = 0;
				foreach (get_contributors_by_project($r->PROJ_ID) as $contrRow) {
					foreach (get_user_detail_byID($contrRow->CONTRIBUTOR_ID) as $contriDetail) {
						if ($i > 0) { echo ", "; } else { $i++; }
						echo $contriDetail->first_name." ".$contriDetail->last_name;
					}
				}
				echo "</div>";	
				echo "<div id='info'>". $r->PROJ_INFO . "</div>\n";	
				echo "</div>\n";
	 		}
	 		
			echo "</div>\n"; 
			echo "</div>";
			
	}	
	echo "</p>\n";	
	if(!$projectFlag) {
		echo "No projects to be displyed";
	}	
	echo $after_widget;
}


/**
 *
 * Get keywords (categoris) for a partcular project
 * @param	int  project ID 
 * @return	resultset	all the keyword information of the project
 *
 */
function get_keywords_by_projID($projID) {
	global $wpdb;	
	$keyTable = $wpdb->prefix."keywords";
	$projKeyTable = $wpdb->prefix."projectkeywords";
	$myrows = $wpdb->get_results("SELECT a.KEYWORD, a.KEY_ID FROM $keyTable as a, $projKeyTable as b WHERE a.KEY_ID = b.KEY_ID AND b.PROJ_ID = $projID");	
	return $myrows;
}


/**
 * Display new project section in the left hand side bar
 */
function display_new_project() {
	global $wpdb;		
	global $current_user;
	$users = $wpdb->prefix."users";
 	$meta = $wpdb->prefix."usermeta";
   $keyword_table = $wpdb->prefix."keywords";
	$projects = $wpdb->prefix."projects";
	$projectkeywords = $wpdb->prefix."projectkeywords";
	$projectcontributors = $wpdb->prefix."projectcontributors";
	
	echo "<div class='projectinput'>";
	echo "<form action='$PHP_SELF' method='POST'>";
	echo '<span onclick="toggleProject(\'addproject\', this, \'[hide]\', \'[Add a new project]\');" style="cursor:pointer"> [Add a new project]</span>'.'<br/>';
	echo "<div id='addproject' style='display:none;'>";
	echo "<label for='project'>Project name:</label>";
	echo "<input type='text' name='txtSynProjectName' id='txtSynProjectName' maxlength='80' style='width:95%'/>\n";
	echo "<br />";
	
	//Contributors
	echo "<label>Contributors:</label><br />\n";
    echo '<select multiple="yes" name="ldbSynProjContributors[]" id="ldbSynProjContributors" size="5" style="width:98%">';
    $query = "SELECT u.id as id, a.meta_value as first_name, b.meta_value AS last_name " .
	         "FROM $users as u, $meta as a, $meta as b " .
			 "WHERE a.user_id = u.id AND b.user_id = u.id " .
			 "AND a.meta_key = 'first_name' AND b.meta_key = 'last_name' AND u.id <> $current_user->ID ". 
			 "ORDER BY first_name";
	$rows = $wpdb->get_results($query);
	
	foreach ($rows as $r) {
	 	echo '<option value="' . $r->id . '">' . $r->first_name . ' ' . $r->last_name .  '</option>';
	} 
	echo "</select></label>\n";

	// Information
	echo "<label for='info'>Information:</label>";
	echo "<textarea rows='4' name='txaSynProjectInfo' id='txaSynProjectInfo' style='width:93%' onkeydown='textProjInfoLimit(this, 200);'></textarea>";
	echo "<br />";
	
	// Keywords (Categories)
	echo "<label >Categories:</label><br />\n";
    echo '<select  multiple="yes" name="ldbSynProjKeywords[]" id="ldbSynProjKeywords" size="5" style="width:98%">' ."\n";       	
 	
    $query = "SELECT * FROM $keyword_table ORDER by KEYWORD";
	$rows = $wpdb->get_results($query);
	
	foreach ($rows as $r) {
	 	echo '<option value="' . $r->KEY_ID . '">' . $r->KEYWORD .  '</option>';
	} 
    echo "</select>\n";
    echo "<strong>New Category:</strong>\n";
    echo "<input type='text' name='txtSynNewCategory' id='txtSynNewCategory' maxlength='50' style='width:95%'/>";
	echo "<button type='submit' name='btnSynProjAdd' id='addproj' value='AddProject' onclick='return validateProject();'>Add Project</button>";
	echo"</form>";
	echo"</div>";	
	echo"</div>";
	
}


/**
 * Convert date time to timestamp
 * @param	string  datetime
 * @return	string timestamp
 */
function convert_project_datetime($str) {
	list($date, $time) = explode(' ', $str);
	list($year, $month, $day) = explode('-', $date);
	list($hour, $minute, $second) = explode(':', $time);
	
	$timestamp = mktime($hour, $minute, $second, $month, $day, $year);
	
	return $timestamp;
}


/**
 * Display edit project section in the left hand side bar
 * @param	int project ID
 */
function display_edit_projects($projID) {
	global $wpdb;		
	global $current_user;
	$users = $wpdb->prefix."users";
 	$meta = $wpdb->prefix."usermeta";
    $keyword_table = $wpdb->prefix."keywords";
	$projects = $wpdb->prefix."projects";
	$projectkeywords = $wpdb->prefix."projectkeywords";
	$projectcontributors = $wpdb->prefix."projectcontributors";
		
	echo "<div>";
	echo "<form action='$PHP_SELF' method='POST' name='frmProject' style='display:block;' >";
	echo "<div  class='dvSynProjEditProject' id='dvSynProjEditProject$projID' style='display:none;'>" . "\n";
	echo "<label for='project'>Project name:</label>";
	echo "<input type='text' name='txtSynEditProjName' maxlength='80' id='txtSynProjName$projID'  style='width:95%'/>";
	echo "<br />";
	
	echo "<label for='info'><strong>Information:</strong></label>";
	echo "<textarea rows='4'  name='txaSynEditProjInfo' id='txaSynProjInfo$projID' onkeydown='textProjInfoLimit(this, 200);'  style='width:93%'></textarea>";
	echo "<br />";
	
	// Keywords (Categories)
	echo "<label >Categories:</label><br />\n";
    echo "<select  multiple='yes' name='ldbSynProjEditKeyword[]' id='ldbSynProjEditKeyword$projID' size='5' style='width:98%'>" ."\n";       	
 	
    $query = "SELECT * FROM $keyword_table ORDER by KEYWORD";
	$rows = $wpdb->get_results($query);
	
	foreach ($rows as $r) {
	 	echo '<option value="' . $r->KEY_ID . '">' . $r->KEYWORD .  '</option>';
	} 			
	
    echo "</select>\n";
    echo "\n<strong>New Category:</strong>\n";
    echo "<input type='text' id='txtSynProjAddEditKeyword$projID' name='txtSynProjAddEditKeyword' maxlength='50' style='width:95%'/>";
	echo "<input type='hidden' name='hdProjID' id='hdProjID$projID'/>";  	
	echo "<button type='submit' name='btnSynProjUpdate' id='btnSynProjUpdate$projID' value='UpdateProject' onclick='return validateEditProject($projID);'>Update</button>";
	echo "<button type='submit' name='btnSynProjDelete' id='btnSynProjDelete$projID' value='DeleteProject' onclick='return confirmProjectDelete();'>Delete</button>";
	echo "</div>";	
	// echo"</div>";
	
	$allUserQuery = "SELECT u.id as id, a.meta_value as first_name, b.meta_value AS last_name " .
	         "FROM $users as u, $meta as a, $meta as b " .
			 "WHERE a.user_id = u.id AND b.user_id = u.id " .
			 "AND a.meta_key = 'first_name' AND b.meta_key = 'last_name' AND u.id <> $current_user->ID ". 
			 "ORDER BY first_name";
	$allUserRows = $wpdb->get_results($allUserQuery);	
	$curContrQuery = "SELECT u.id as id, a.meta_value as first_name, b.meta_value AS last_name " .
	         "FROM $users as u, $meta as a, $meta as b " .
			 "WHERE EXISTS (SELECT CONTRIBUTOR_ID FROM $projectcontributors WHERE $projectcontributors.CONTRIBUTOR_ID = u.ID AND $projectcontributors.PROJ_ID = $projID) AND a.user_id = u.id AND b.user_id = u.id " .
			 "AND a.meta_key = 'first_name' AND b.meta_key = 'last_name'". 
			 "ORDER BY first_name";
	$currentContrRows = $wpdb->get_results($curContrQuery);
	echo "<br /><div class='dvSynProjAddContributors' id='dvSynProjAddContributors$projID' style='display:none'>";
	echo "<label>Contributors:</label>";	
	echo "<div class='selectContributors'>";
	foreach ($allUserRows as $allContRow) {
		foreach ($currentContrRows as $curContRow) {			
			if($allContRow->id == $curContRow->id) {
				
				echo "<input type='checkbox' class='synCheck' checked name='chkAllUsers[]' value='$allContRow->id'>".$allContRow->first_name." ".$allContRow->last_name."<br />";
				break;
			}
			else {
				echo "<input type='checkbox' class='synCheck' name='chkAllUsers[]' value='$allContRow->id'>".$allContRow->first_name." ".$allContRow->last_name."<br />";
				break;
			}			
		}
	}	
	echo "</div>";
	echo "<button type='submit' name='btnSynProjUpdateContributor' id='btnSynProjUpdateContributor$projID' value='UpdateProjectContributor'>Apply Changes</button>";
	echo "</div>";
	echo"</form>";	
	echo "</div>";
}


/**
 * Add new project to the database
 */
function add_new_project() {
	global $wpdb;
	global $current_user;
	$projects = $wpdb->prefix."projects";	 
	$keyword_table = $wpdb->prefix."keywords";
	$projectkeywords = $wpdb->prefix."projectkeywords";
	$projectcontributors = $wpdb->prefix."projectcontributors";
	$projectName = $_POST['txtSynProjectName'];
	$projectInfo = $_POST['txaSynProjectInfo'];	
	
	// Project table
	$wpdb->query("INSERT INTO $projects(PROJ_NAME, MANAGER_ID, PROJ_DATE, PROJ_INFO, IS_DELETED)
	        VALUES('".cleanProjectInput(trim($projectName))."', '$current_user->ID', NOW(), '".cleanProjectInput(trim($projectInfo))."', '0')");
			
	$PROJ_ID = mysql_insert_id();
	
	// Keyword table
	if(isset($_POST[txtSynNewCategory]) && (trim($_POST[txtSynNewCategory]) != "")) {
		$insertKeyword = "INSERT INTO $keyword_table(KEYWORD, CREATOR_ID, CREATED_DATE) VALUES('".cleanProjectInput(trim($_POST[txtSynNewCategory]))."', $current_user->ID, NOW())";
		$wpdb->query($insertKeyword);
		$NEW_KEY_ID = mysql_insert_id();
		$insertProjKeyword = "INSERT INTO $projectkeywords(PROJ_ID, KEY_ID) VALUES($PROJ_ID, $NEW_KEY_ID)";
		$wpdb->query($insertProjKeyword);
	}
		
		
	// Project Keyword table		
	if(isset($_POST[ldbSynProjKeywords]) || ($_POST[ldbSynProjKeywords] != "")) {
		for ($g=0; $g<sizeof($_POST[ldbSynProjKeywords]); $g++) {
			$KEY_ID = $_POST[ldbSynProjKeywords][$g];
			$insertProjKeyword = "INSERT INTO $projectkeywords(PROJ_ID, KEY_ID) VALUES($PROJ_ID, $KEY_ID)";
			$wpdb->query($insertProjKeyword);					
		}				
	}		
		
	// Project Contributor table
	if(isset($_POST[ldbSynProjContributors]) || ($_POST[ldbSynProjContributors]!="")) {
		for ($g=0; $g<sizeof($_POST[ldbSynProjContributors]); $g++) {
			$CONTRIBUTOR_ID = $_POST[ldbSynProjContributors][$g];
			$insertContriKeyword = "INSERT INTO $projectcontributors(CONTRIBUTOR_ID, PROJ_ID) VALUES($CONTRIBUTOR_ID, $PROJ_ID)";
			$wpdb->query($insertContriKeyword);						
		}				
	}		
	$wpdb->query("INSERT INTO $projectcontributors(CONTRIBUTOR_ID, PROJ_ID) VALUES($current_user->ID, $PROJ_ID)");
	
}


/**
 * Update the details of the selected project 
 */
function update_project() {
	global $wpdb;
	global $current_user;
	$projects = $wpdb->prefix."projects";	 
	$projKeywords = $wpdb->prefix."projectkeywords";
	$keyword_table = $wpdb->prefix."keywords";
	// Project table
	$wpdb->query("UPDATE $projects SET PROJ_NAME='".cleanProjectInput(trim($_POST[txtSynEditProjName]))."', MANAGER_ID = $current_user->ID, PROJ_DATE=NOW(), PROJ_INFO='".cleanProjectInput(trim($_POST[txaSynEditProjInfo]))."' WHERE PROJ_ID =$_POST[hdProjID]");

	$wpdb->query("DELETE FROM $projKeywords WHERE PROJ_ID=$_POST[hdProjID]");
	if(isset($_POST[txtSynProjAddEditKeyword]) && (trim($_POST[txtSynProjAddEditKeyword])!="")) {
		$insertKeyword = "INSERT INTO $keyword_table(KEYWORD, CREATOR_ID, CREATED_DATE) VALUES('".cleanProjectInput(trim($_POST[txtSynProjAddEditKeyword]))."', $current_user->ID, NOW())";
		$wpdb->query($insertKeyword);
		$NEW_KEY_ID = mysql_insert_id();		
		
		$insertProjKeyword = "INSERT INTO $projKeywords(PROJ_ID, KEY_ID) VALUES($_POST[hdProjID], $NEW_KEY_ID)";
		$wpdb->query($insertProjKeyword);
	}
	if(isset($_POST[ldbSynProjEditKeyword]) && ($_POST[ldbSynProjEditKeyword]!="")) {
		foreach ($_POST[ldbSynProjEditKeyword] as $KEY_ID) {
			$insertProjKeyword = "INSERT INTO $projKeywords(PROJ_ID, KEY_ID) VALUES($_POST[hdProjID], $KEY_ID)";
			$wpdb->query($insertProjKeyword);					
		}				
	}
}


/**
 *
 * Delete a selected project from the system
 * @param	int  project ID
 *
 */
function delete_project($projID) {
	global $wpdb;
	$projTable = $wpdb->prefix."projects";
	$projKeywordTable = $wpdb->prefix."projectkeywords";
	$projContributorTable = $wpdb->prefix."projectcontributors";
	$deleteSchema = "UPDATE $projTable SET IS_DELETED='1' WHERE PROJ_ID = $projID";
	$wpdb->query($deleteSchema);
	
	// Delete all the keywords of the selected project
	$wpdb->query("DELETE FROM $projKeywordTable WHERE PROJ_ID = $projID");
	// Delete all the contributors of the selected project
	$wpdb->query("DELETE FROM $projContributorTable WHERE PROJ_ID = $projID");
	
	
}

/**
 *
 * Add/Remove contributors to/from the selected project
 * @param	int  project ID
 *
 */
function modify_project_contributors($projID) {
	global $wpdb;
	global $current_user;
	$projContributorTable = $wpdb->prefix."projectcontributors";		
	$wpdb->query("DELETE FROM $projContributorTable WHERE PROJ_ID = $projID");
	$wpdb->query("INSERT INTO $projContributorTable(CONTRIBUTOR_ID, PROJ_ID) VALUES($current_user->ID, $projID)");
	for($k=0; $k<sizeof($_POST[chkAllUsers]); $k++) {
		$contriID = $_POST[chkAllUsers][$k];
		$insertSchema = "INSERT INTO $projContributorTable(CONTRIBUTOR_ID, PROJ_ID) VALUES($contriID, $projID)";
		$wpdb->query($insertSchema);		
	}	
}




/**
 *
 * Get projects of the selected user
 * @return	resultset  result set of projects
 *
 */
function get_projects_by_contributorid($contributorID) {
	global $wpdb;
	$projContTable = $wpdb->prefix."projectcontributors";
	$projTable = $wpdb->prefix."projects";
	$myrows = $wpdb->get_results("SELECT DISTINCT $projTable.PROJ_ID, $projTable.PROJ_NAME, $projTable.PROJ_INFO, $projTable.PROJ_DATE, $projTable.MANAGER_ID
	FROM $projTable, $projContTable WHERE $projContTable.PROJ_ID = $projTable.PROJ_ID
	AND $projContTable.CONTRIBUTOR_ID = $contributorID ORDER BY $projTable.PROJ_NAME ASC");	
	return $myrows;	
}


/**
 *
 * Sanitize Database Inputs
 * @param	string	input applying sanitize
 *
 */
function cleanProjectInput($input) {

  $search = array(
    '@<script[^>]*?>.*?</script>@si',   // Strip out javascript
    '@<[\/\!]*?[^<>]*?>@si',            // Strip out HTML tags
    '@<style[^>]*?>.*?</style>@siU',    // Strip style tags properly
    '@<![\s\S]*?--[ \t\n\r]*>@'         // Strip multi-line comments
  );

    $output = preg_replace($search, '', $input);
    return $output;
  }


// register syn_proj_mgmt widget  
register_sidebar_widget('Synergy Project Management', 'widget_syn_proj_mgmt');


/**
 * Install the syn_proj_mgmt plugin to the system. It will create 
 * all the tables in the wordpress database.
 */
function project_install()
{
    global $wpdb;    
    $user_table = $wpdb->prefix."users";    
    $proj_table = $wpdb->prefix."projects";
        
	// $dropstructure = "DROP TABLE IF EXISTS $proj_table";
	
	$wpdb->query($dropstructure);    
    $structure = "CREATE TABLE $proj_table(
        PROJ_ID INT(9) AUTO_INCREMENT,
        PROJ_NAME VARCHAR(50),
    	MANAGER_ID INT(9),
    	PROJ_DATE DATETIME ,
    	PROJ_INFO VARCHAR(500),	
    	IS_DELETED TINYINT(1) DEFAULT 0,	
    	UNIQUE KEY PROJ_ID (PROJ_ID),
    	FOREIGN KEY (MANAGER_ID) REFERENCES $user_table (ID) ON UPDATE CASCADE ON DELETE CASCADE   			
    );";
    
    $wpdb->query($structure); 
    
    $wpdb->query("INSERT INTO $proj_table(PROJ_NAME,PROJ_DATE,PROJ_INFO,IS_DELETED)
        VALUES('Animals', '2010-02-11 00:00:01', 'This project has all data related to Elephants', '0')");
    $wpdb->query("INSERT INTO $proj_table(PROJ_NAME,PROJ_DATE,PROJ_INFO,IS_DELETED)
        VALUES('Cars', '2010-02-10 08:36:01', 'This project has all data related to Ferrari', '0')");
    $wpdb->query("INSERT INTO $proj_table(PROJ_NAME,PROJ_DATE,PROJ_INFO,IS_DELETED)
        VALUES('Seeds', '2010-02-07 00:00:01', 'This project has all data related to Corn Seeds', '0' )");
    $wpdb->query("INSERT INTO $proj_table(PROJ_NAME,PROJ_DATE,PROJ_INFO,IS_DELETED)
        VALUES('Landscapes', '2010-02-01 00:00:01', 'This project focuses on landscapes in different weather conditions and at different times of the day', '0')");
               
  $key_table = $wpdb->prefix."keywords";
    
	// $dropstructure1 = "DROP TABLE IF EXISTS $key_table";
	
	$wpdb->query($dropstructure1);
    
    $structure1 = "CREATE TABLE $key_table (
        KEY_ID INT(9) AUTO_INCREMENT PRIMARY KEY,
    	KEYWORD VARCHAR(50),
    	CREATOR_ID INT(9),
    	CREATED_DATE DATETIME,						
    	FOREIGN KEY (KEYWORD) REFERENCES $proj_table (KEYWORD) ON UPDATE CASCADE ON DELETE CASCADE,
    	FOREIGN KEY (CREATOR_ID) REFERENCES $user_table (ID) ON UPDATE CASCADE ON DELETE CASCADE
    );";
    
    $wpdb->query($structure1); 
        
    $proj_cont_table = $wpdb->prefix."projectcontributors";
    
	// $dropstructure2 = "DROP TABLE IF EXISTS $proj_cont_table";
	
	$wpdb->query($dropstructure2);
    
    $structure2 = "CREATE TABLE $proj_cont_table (
        PROJ_ID INT(9) ,
        CONTRIBUTOR_ID INT(9) NOT NULL ,
        FOREIGN KEY (PROJ_ID) REFERENCES $proj_table(PROJ_ID) ON UPDATE CASCADE ON DELETE CASCADE, 
		PRIMARY KEY (PROJ_ID, CONTRIBUTOR_ID)
    );";
      
    $wpdb->query($structure2); 
    
    $proj_key_table = $wpdb->prefix."projectkeywords";
    
	// $dropstructure3 = "DROP TABLE IF EXISTS $proj_key_table";
	
	$wpdb->query($dropstructure3);
    
    $structure3 = "CREATE TABLE $proj_key_table (
        PROJ_ID INT(9) ,
        KEY_ID INT(9) ,						
    	FOREIGN KEY (PROJ_ID) REFERENCES $proj_table (PROJ_ID) ON UPDATE CASCADE ON DELETE CASCADE,
    	FOREIGN KEY (KEY_ID) REFERENCES $key_table (KEY_ID) ON UPDATE CASCADE ON DELETE CASCADE
    );";
    
    $wpdb->query($structure3);
            
}

// register plugin and install it to the wordpress
add_action("activate_syn_proj_mgmt/syn_proj_mgmt.php", "project_install");


?>
